Imports DTO
Imports System.Data.OleDb
Public Class PhanCongDAO
    Inherits AbstractDAO
    Public Sub New()

    End Sub
    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "select * from PhanCong"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function
    Public Sub CapNhanBang(ByVal dt As DataTable)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from PhanCong"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        cn.Close()
    End Sub
    Public Function LayDanhSach() As IList
        Dim ds As New ArrayList()
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from PhanCong"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim pcDTO As New PhanCongDTO
            pcDTO.MaPhanCong = dr("MaPhanCong")
            pcDTO.MaLopHoc = dr("MaLopHoc")
            pcDTO.MaMonHoc = dr("MaMonHoc")
            pcDTO.MaGiaoVien = dr("MaGiaoVien")
            pcDTO.SoTietHocTuan = dr("SoTietHocTuan")
            pcDTO.SoTietLienTiepToiThieu = dr("SoTietLienTiepToiThieu")
            pcDTO.SoTietLienTiepToiDa = dr("SoTietLienTiepToiDa")
            pcDTO.SoBuoiHocToiThieu = dr("SoBuoiHocToiThieu")
            pcDTO.SoBuoiHocToiDa = dr("SoBuoiHocToiDa")


            ds.Add(pcDTO)
        End While
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Function TiemKiemTheoMa(ByVal maphancong As String) As PhanCongDTO
        Dim pcDTO As New PhanCongDTO
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from PhanCong Where MaPhanCong = " & maphancong
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            pcDTO.MaPhanCong = dr("MaPhanCong")
            pcDTO.MaLopHoc = dr("MaLopHoc")
            pcDTO.MaMonHoc = dr("MaMonHoc")
            pcDTO.MaGiaoVien = dr("MaGiaoVien")
            pcDTO.SoTietHocTuan = dr("SoTietHocTuan")
            pcDTO.SoTietLienTiepToiThieu = dr("SoTietLienTiepToiThieu")
            pcDTO.SoTietLienTiepToiDa = dr("SoTietLienTiepToiDa")
            pcDTO.SoBuoiHocToiThieu = dr("SoBuoiHocToiThieu")
            pcDTO.SoBuoiHocToiDa = dr("SoBuoiHocToiDa")
        End While
        dr.Close()
        cn.Close()
        Return pcDTO
    End Function
    Public Sub Them(ByVal pcDTO As PhanCongDTO)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Insert into PhanCong(MaPhanCong, MaLopHoc, MaMonHoc, MaGiaoVien, SoTietHocTuan, SoTietHocLienTiepToiThieu, SoTietHocLienTiepToiDa, SoBuoiHocToiThieu, SoBuoiHocToiDa) value= " & _
        " ( '" & pcDTO.MaPhanCong & "', '" & pcDTO.MaLopHoc & "', '" & pcDTO.MaMonHoc & "', '" & pcDTO.MaGiaoVien & "', " & _
        pcDTO.SoTietHocTuan & ", " & pcDTO.SoTietLienTiepToiThieu & ", " & pcDTO.SoTietLienTiepToiDa & ", " & pcDTO.SoBuoiHocToiThieu & ", " & pcDTO.SoBuoiHocToiDa & ")"
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        strSQL = "Select @@INDENTITY"
        cmd = New OleDbCommand(strSQL, cn)
        pcDTO.MaPhanCong = cmd.ExecuteScalar()
        cn.Close()
    End Sub
    Public Sub XoaTheoMa(ByVal maphancong As String)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Delete from PhanCong Where MaPhanCong= " & maphancong
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

    End Sub
    Public Sub Sua(ByVal pcDTO As PhanCongDTO)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Update PhanCong Set " & _
                " MaLopHoc= '" & pcDTO.MaLopHoc & _
                "' and MaMonHoc= '" & pcDTO.MaMonHoc & "' and MaGiaoVien= '" & pcDTO.MaGiaoVien & _
                "' and SoTietHocTuan= " & pcDTO.SoTietHocTuan & " and SoTietLienTiepToiThieu= " & pcDTO.SoTietLienTiepToiThieu & _
                " and SoTietLienTiepToiDa= " & pcDTO.SoTietLienTiepToiDa & " and SoBuoiHocToiThieu= " & pcDTO.SoBuoiHocToiThieu & _
                " and SoBuoiHocToiDa= " & pcDTO.SoBuoiHocToiDa & "Where MaPhanCong= '" & pcDTO.MaPhanCong & " '"
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub
    Public Function TiemKiemTheoMaMonHoc(ByVal mamonhoc As String) As PhanCongDTO
        Dim pcDTO As New PhanCongDTO
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = " Select MaGiaoVien,SoTietHocTuan,SoTietLienTiepToiThieu,SoTietLienTiepToiDa,SoBuoiHocToiThieu,SoBuoiHocToiDa Where MaMonHoc= " & mamonhoc
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While (dr.Read())
            pcDTO.MaGiaoVien = dr("MaGiaoVien")
            pcDTO.SoTietHocTuan = dr("SoTietHocTuan")
            pcDTO.SoTietLienTiepToiThieu = dr("SoTietLienTiepToiThieu")
            pcDTO.SoTietLienTiepToiDa = dr("SoTietLienTiepToiDa")
            pcDTO.SoBuoiHocToiThieu = dr("SoBuoiHocToiThieu")
            pcDTO.SoBuoiHocToiDa = dr("SoBuoiHocToiDa")
        End While
        dr.Close()
        cn.Close()
        Return pcDTO
    End Function
    Public Function TiemKiemTheoMaLopHoc(ByVal malophoc As String) As PhanCongDTO
        Dim pcDTO As New PhanCongDTO
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = " Select MaGiaoVien,SoTietHocTuan,SoTietLienTiepToiThieu,SoTietLienTiepToiDa,SoBuoiHocToiThieu,SoBuoiHocToiDa Where MaMonHoc= " & malophoc
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While (dr.Read())
            pcDTO.MaGiaoVien = dr("MaGiaoVien")
            pcDTO.SoTietHocTuan = dr("SoTietHocTuan")
            pcDTO.SoTietLienTiepToiThieu = dr("SoTietLienTiepToiThieu")
            pcDTO.SoTietLienTiepToiDa = dr("SoTietLienTiepToiDa")
            pcDTO.SoBuoiHocToiThieu = dr("SoBuoiHocToiThieu")
            pcDTO.SoBuoiHocToiDa = dr("SoBuoiHocToiDa")
        End While
        dr.Close()
        cn.Close()
        Return pcDTO
    End Function
End Class
